﻿using System;
using System.Data;
using System.Text;
using System.Data.Odbc;
using System.Windows.Forms;
using System.Collections.Generic;

namespace Furniture_Rental_System
{   
    /// <summary>
    /// Accesses database whenever neccesary.
    /// Builds SQL queries using data received from the user.
    /// </summary>
    class DataBaseAccessor
    {

        /// <summary>
        /// Declare objects to access the MySQL database.
        OdbcConnection MyConnection;
        OdbcCommand MyCommand;
        OdbcDataReader MyDataReader;


        #region Constructor

        /// <summary>
        /// Creates connection string and sets the connection.
        /// </summary>
        public DataBaseAccessor()
        {
            try
            {
                //Setup connection parameter string: MyConString
                string MyConString = "DRIVER={MySQL ODBC 5.1 Driver};" +
                     "SERVER=turing.cs.westga.edu;" +
                     "PORT=3306;" +
                     "DATABASE=cs3230f10c;" +
                     "UID=cs3230f10c;" +
                     "PASSWORD=vWz7sFsd5NV3PW3J";

                //Set the connection
                MyConnection = new OdbcConnection(MyConString);
            }
            //Catch any exception that may occur during the connection
            catch (Exception e)
            {
                MessageBox.Show("An unexpected error has occured while connecting to the furniture rental system. \n" + e.Message);
            }
        }

        #endregion

    #region Database Accessors
        /// <summary>
        /// Accesses the database and validates userID and password got from user.
        /// </summary>
        /// <param name="userID"> Username obtained from user </param>
        /// <param name="password"> Password obtained from user </param>
        /// <returns> Returns boolen value: True if password matches that stored in the database
        ///                                 False if password does not match. </returns>
        public bool checkDataBaseForEmployeeIDAndPassword(String userID, String password)
        {
            try
            {
                // Opens connection to Database
                MyConnection.Open();

                // Builds SQL query
                OdbcCommand MyCommand = new OdbcCommand("SELECT Password FROM EMPLOYEE WHERE userId = " + userID + ";", MyConnection);
                
                // Declares and initialized data reader.
                OdbcDataReader MyDataReader;
                MyDataReader = MyCommand.ExecuteReader();

                String passwordFromDataBase = "";

                // Loops through the values returned by the database and initializes obtained password to "passwordFromDataBase"
                while (MyDataReader.Read())
                {
                    passwordFromDataBase = (MyDataReader.GetString(0));
                }
                
                //Close all resources and disconnect
                MyDataReader.Close();
                MyConnection.Close();

                // Checks if the password provided by user matches the password associated with the username provided by the user.
                if (password == passwordFromDataBase)
                    return true;

                return false;
            }
            catch (Exception)
            {
                return false;
            }
 


        }

        public bool checkDataBaseForAdminIDAndPassword(String userID, String password)
        {
            try
            {
                // Opens connection to Database
                MyConnection.Open();

                // Builds SQL query
                OdbcCommand MyCommand = new OdbcCommand("SELECT Password FROM SYSTEM_ADMIN WHERE userId = " + userID + ";", MyConnection);

                // Declares and initialized data reader.
                OdbcDataReader MyDataReader;
                MyDataReader = MyCommand.ExecuteReader();

                String passwordFromDataBase = "";

                // Loops through the values returned by the database and initializes obtained password to "passwordFromDataBase"
                while (MyDataReader.Read())
                {
                    passwordFromDataBase = (MyDataReader.GetString(0));
                }

                //Close all resources and disconnect
                MyDataReader.Close();
                MyConnection.Close();

                // Checks if the password provided by user matches the password associated with the username provided by the user.
                if (password == passwordFromDataBase)
                    return true;

                return false;
            }
            catch (Exception)
            {
                return false;
            }



        }
		
        /// <summary>
        /// Accesses the database and adds a new member. Member information got via the form used by the user
        /// </summary>
        /// <param name="fname"> First Name </param>
        /// <param name="lname"> Last Name </param>
        /// <param name="ssn"> Social Security Number</param>
        /// <param name="phoneNumber"></param>
        /// Address: <param name="houseNo"></param>
        /// <param name="street"></param>
        /// <param name="city"></param>
        /// <param name="state"></param>
        /// <param name="zip"></param>
        /// <returns> Returns a boolean value: True if add was successful. False if not or in case of an exception. </returns>
		public bool addNewMember(string fname, string lname, string ssn, string phoneNumber, string houseNo, string street, 
            string city, string state, string zip)
        {

            try
            {
                // Opens connection to Database
                MyConnection.Open();

                // Builds string for SQL query
                String newMember = "INSERT INTO MEMBER VALUES (NULL, '" + fname + "' , '" + lname + "' , '" + ssn + "' , '" + 
                    phoneNumber + "' , '" + houseNo + "' , '" + street + "' , '" + city + "' , '" + state + "' , '" + zip + "');";

                // Builds SQL query and executes the query
                MyCommand = new OdbcCommand(newMember, MyConnection);
                MyCommand.ExecuteNonQuery();

                // Closes connection to the database and returns true. Implying a succesful add.
                MyConnection.Close();
                return true;
            }
             // Catches any excptions that may occur
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                return false;
            }
        }

        /// <summary>
        /// Accesses the database and and looks up furniture based off various search criteria. Search critera obtained from user via the form.
        /// </summary>
        /// <param name="types"> A array of strings containing the field names to be accesed </param>
        /// <param name="values">A array of strings containing the values of the fields in the corresponding index in "types" </param>
        /// <returns> Returns string containing search results. </returns>

        /// <summary>
        /// Accesses the database and and looks up furniture based off various search criteria. Search critera obtained from user via the form.
        /// </summary>
        /// <param name="types"> A array of strings containing the field names to be accesed </param>
        /// <param name="values">A array of strings containing the values of the fields in the corresponding index in "types" </param>
        /// <returns> Returns string containing search results. </returns>
        public List<ListViewItem> furnitureLookUp(string[] types, string[] values)
        {
            try
            {
                //Opens the connection to the database
                MyConnection.Open();

                // Declares and build the first half of the SQL query. The "WHERE" part of the query is worked on in the for loop.
                List<ListViewItem> returnList = new List<ListViewItem>();
                string sqlStatement = "SELECT F.itemNumber, F.fineRate, F.Manufacturer, F.Color, F.Style, F.Category, F.isAvailable " +
                    "FROM FURNITURE_ITEM AS F " +
                    "WHERE ";

                sqlStatement = BuildConjunciveSqlStatement(types, values, sqlStatement);

                //Declares and initializes new OdbcCommand
                MyCommand = new OdbcCommand(sqlStatement, MyConnection);

                //Initializes a data reader
                MyDataReader = MyCommand.ExecuteReader();

                //Runs through the returned data and builds a string that holds the search results.
                while (MyDataReader.Read())
                {
                    //Populates each listViewItem with the information about a furniture item.
                    ListViewItem furnitureList = new ListViewItem(MyDataReader.GetString(0));
                    furnitureList.SubItems.Add(MyDataReader.GetString(1));
                    furnitureList.SubItems.Add(MyDataReader.GetString(2));
                    furnitureList.SubItems.Add(MyDataReader.GetString(3));
                    furnitureList.SubItems.Add(MyDataReader.GetString(4));
                    furnitureList.SubItems.Add(MyDataReader.GetString(5));
                    furnitureList.SubItems.Add(MyDataReader.GetString(6));
                    returnList.Add(furnitureList);
                }

                //Close all resources and disconnects from the database
                MyDataReader.Close();
                MyConnection.Close();

                return returnList;
            }
            // Catches any errors that may have occured
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
            }
        }

        public List<ListViewItem> furnitureLookUpAvailableItem(string[] types, string[] values)
        {
            try
            {
                //Opens the connection to the database
                MyConnection.Open();

                // Declares and build the first half of the SQL query. The "WHERE" part of the query is worked on in the for loop.
                List<ListViewItem> returnList = new List<ListViewItem>();
                string sqlStatement = "SELECT F.itemNumber, F.fineRate, F.Manufacturer, F.Color, F.Style, F.Category, F.isAvailable " +
                    "FROM FURNITURE_ITEM AS F " +
                    "WHERE ";

                sqlStatement = BuildConjunciveSqlStatement(types, values, sqlStatement);

                //Declares and initializes new OdbcCommand
                MyCommand = new OdbcCommand(sqlStatement, MyConnection);

                //Initializes a data reader
                MyDataReader = MyCommand.ExecuteReader();

                //ListViewItem furnitureList = new ListViewItem();

                //Runs through the returned data and builds a string that holds the search results.
                while (MyDataReader.Read())
                {
                    //only add items that are available
                    //if (MyDataReader.GetString(6) == "y")
                    //{
                        //Populates each listViewItem with the information about a furniture item.
                        //ListViewItem furnitureList = new ListViewItem(MyDataReader.GetString(0));
                        //ListViewItem furnitureList = new ListViewItem();
                        ListViewItem furnitureList = new ListViewItem(MyDataReader.GetString(0)); 
                        furnitureList.SubItems.Add(MyDataReader.GetString(1));
                        furnitureList.SubItems.Add(MyDataReader.GetString(2));
                        furnitureList.SubItems.Add(MyDataReader.GetString(3));
                        furnitureList.SubItems.Add(MyDataReader.GetString(4));
                        furnitureList.SubItems.Add(MyDataReader.GetString(5));
                        String checkAvailable = MyDataReader.GetString(6);
                        furnitureList.SubItems.Add(checkAvailable);

                        if (checkAvailable == "y")
                        {
                            returnList.Add(furnitureList);
                            return returnList;
                        }
                        
                    //}
                    
                }

                //Close all resources and disconnects from the database
                MyDataReader.Close();
                MyConnection.Close();

                return returnList;
            }
            // Catches any errors that may have occured
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
            }
        }

        private static string BuildConjunciveSqlStatement(string[] types, string[] values, string sqlStatement)
        {
            // Runs through types.
            for (int i = 0; i < types.Length; i++)
            {
                string type = types[i];
                string value = values[i];

                // Skips to next run i the value of types at i is null. Note that types[i] == null implies that values[i] is null as well.
                if (types[i] == null)
                {
                    continue;
                }

                if (i == 0)
                {
                    sqlStatement = sqlStatement + "F." + type + " = " + int.Parse(value) + " ";
                    sqlStatement = sqlStatement + " AND ";
                }

                else
                {
                    // Builds second half of the SQL query.
                    sqlStatement = sqlStatement + "F." + type + " = '" + value + "' ";
                    sqlStatement = sqlStatement + " AND ";
                }
            }

            sqlStatement = sqlStatement.Substring(0, (sqlStatement.Length - 5));
            sqlStatement += ";";
            return sqlStatement;
        }

        public List<string> memberLookUpByPhoneNumber(string phoneNumber)
        {
            try
            {
                //Opens the connection to the database
                MyConnection.Open();

                // Declares and build the first half of the SQL query. The "WHERE" part of the query is worked on in the for loop.
                string sqlStatement = "SELECT M.memId, M.fName, M.lName, M.ssn, M.phone, M.houseNumber, M.street, M.city, M.state, M.zip " +
                    "FROM MEMBER AS M " +
                    "WHERE M.phone = " + "'" + phoneNumber + "';";

                //Declares and initializes new OdbcCommand
                MyCommand = new OdbcCommand(sqlStatement, MyConnection);

                //Initializes a data reader
                MyDataReader = MyCommand.ExecuteReader();
                List<string> memberList = new List<string>();

                //Runs through the returned data and builds a string that holds the search results.
                while (MyDataReader.Read())
                {
                    //Populates each listViewItem with the information about a furniture item.
                    memberList.Add(MyDataReader.GetString(0));
                    memberList.Add(MyDataReader.GetString(1));
                    memberList.Add(MyDataReader.GetString(2));
                    memberList.Add(MyDataReader.GetString(3));
                    memberList.Add(MyDataReader.GetString(4));
                    memberList.Add(MyDataReader.GetString(5));
                    memberList.Add(MyDataReader.GetString(6));
                    memberList.Add(MyDataReader.GetString(7));
                    memberList.Add(MyDataReader.GetString(8));
                    memberList.Add(MyDataReader.GetString(9));
                }

                //Close all resources and disconnects from the database
                MyDataReader.Close();
                MyConnection.Close();
                return memberList;
            }

            catch (Exception e)
            {
                MessageBox.Show("An unexpected error has occured while connecting to the furniture rental system. \n" + e.Message);
                return null;
            }
        }

        
        public bool memberLookUpByMemberID(string memberID)
        {
            try
            {
                //Opens the connection to the database
                MyConnection.Open();

                // Declares and build the first half of the SQL query. The "WHERE" part of the query is worked on in the for loop.
                string sqlStatement = "SELECT M.memId " +
                    "FROM MEMBER AS M " +
                    "WHERE M.memId = " + "'" + memberID + "';";

                //Declares and initializes new OdbcCommand
                MyCommand = new OdbcCommand(sqlStatement, MyConnection);

                //Initializes a data reader
                MyDataReader = MyCommand.ExecuteReader();
                List<string> memberList = new List<string>();

                //Runs through the returned data and builds a string that holds the search results.
                while (MyDataReader.Read())
                {
                    //Populates each listViewItem with the information about a furniture item.
                    memberList.Add(MyDataReader.GetString(0));
                }

                //Close all resources and disconnects from the database
                MyDataReader.Close();
                MyConnection.Close();
                if (memberList.Count != 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

            catch (Exception e)
            {
                MessageBox.Show("An unexpected error has occured while connecting to the furniture rental system. \n" + e.Message);
                return false;
            }
        }

        public bool addFurnitureItem(string fineRate, string Manufacturer, string Color, string Style, string Category)
        {

            try
            {
                // Opens connection to Database
                MyConnection.Open();

                // Builds string for SQL query
                String newMember = "INSERT INTO FURNITURE_ITEM VALUES (NULL, '" + fineRate + "' , '" + Manufacturer + "' , '" + Color + "' , '" +
                    Style + "' , '" + Category + "' , 'y');";

                // Builds SQL query and executes the query
                MyCommand = new OdbcCommand(newMember, MyConnection);
                MyCommand.ExecuteNonQuery();

                // Closes connection to the database and returns true. Implying a succesful add.
                MyConnection.Close();
                return true;
            }
            // Catches any excptions that may occur
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                return false;
            }
        }

        public bool addNewRental(string[] rentalDetails, DateTime dateTime)
        {
            DateTime dt = dateTime;
            string year = dt.Year.ToString();
            string day = dt.Day.ToString();
            string month = dt.Month.ToString();
            string hours = dt.Hour.ToString();
            string mins = dt.Minute.ToString();
            string sec = dt.Second.ToString();
            string checkOutDate = year + month.PadLeft(2,'0') + day.PadLeft(2,'0') + hours + mins + sec;
            string duedate = year + (dt.AddMonths(1).Month.ToString().PadLeft(2,'0')) + day.PadLeft(2,'0') ;
            // +"T" + hours + ":" + mins + ":" + sec;

            try
            {
                // Opens connection to Database
                MyConnection.Open();

                // Builds string for SQL query
                String newMember = "INSERT INTO RENTAL VALUES (NULL, '" + rentalDetails.GetValue(0) + "' , '" + rentalDetails.GetValue(1) + "', NULL ,'" + rentalDetails.GetValue(2) + "' , '" + duedate + "' , NULL, '" + checkOutDate + "');";

                // Builds SQL query and executes the query
                MyCommand = new OdbcCommand(newMember, MyConnection);
                MyCommand.ExecuteNonQuery();
             
                String changeFurnitureAvailability = "UPDATE FURNITURE_ITEM SET isAvailable = 'n' WHERE itemNumber IN (SELECT itemnumber FROM RENTAL WHERE itemnumber =" + "'" + rentalDetails.GetValue(2) + "')";

                MyCommand = new OdbcCommand(changeFurnitureAvailability, MyConnection);
                MyCommand.ExecuteNonQuery();

                // Closes connection to the database and returns true. Implying a succesful add.
                MyConnection.Close();
                return true;
            }
            // Catches any exceptions that may occur
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                return false;
            }
        }

        public double addNewCheckin(DateTime checkin, String checkInempId, int rentalNumber)
        {
            DateTime dt = checkin;
            double fine = 0;
            string year = dt.Year.ToString();
            string day = dt.Day.ToString();
            string month = dt.Month.ToString();
            string hours = dt.Hour.ToString();
            string mins = dt.Minute.ToString();
            string sec = dt.Second.ToString();
            string checkIndate = year + (month.ToString().PadLeft(2, '0')) + day.PadLeft(2, '0');
            try
            {
                //Opens the connection to the database
                MyConnection.Open();

                // Declares and build the first half of the SQL query. The "WHERE" part of the query is worked on in the for loop.
                string sqlStatement = "SELECT R.duedate, F.fineRate, R.itemnumber, F.isAvailable FROM RENTAL As R, FURNITURE_ITEM As F WHERE R.rentalnumber = " + "'" + rentalNumber + 
                    "' AND R.itemnumber = F.itemNumber;";

                //Declares and initializes new OdbcCommand
                MyCommand = new OdbcCommand(sqlStatement, MyConnection);

                //Initializes a data reader
                MyDataReader = MyCommand.ExecuteReader();

                DateTime dDate = new DateTime();
                String fineRate = "";
                String itemNumber = "";
                String isAvailable = "";
                //Runs through the returned data and builds a string that holds the search results.
                while (MyDataReader.Read())
                {
                    dDate = MyDataReader.GetDate(0);
                    fineRate = MyDataReader.GetString(1);
                    itemNumber = MyDataReader.GetString(2);
                    isAvailable = MyDataReader.GetString(3);
                }

                if (checkin > dDate)
                {
                    double days = checkin.Subtract(dDate).TotalDays;
                    days = Math.Round(days, 0);
                    fine = (checkin.Subtract(dDate).TotalDays) * double.Parse(fineRate);
                    MessageBox.Show("You owe a fine totalling $" + Math.Round(fine,0));
                }
                if (!isAvailable.Equals("y"))
                {
                    String updateSQL = "UPDATE RENTAL SET checkinempid = '" + checkInempId + "', checkindate = '" + checkIndate + "' WHERE rentalNumber = " + rentalNumber;
                    String updateSQL2 = "UPDATE FURNITURE_ITEM SET isAvailable = 'y' WHERE itemNumber IN (SELECT itemnumber FROM RENTAL WHERE itemnumber =" + "'" + itemNumber + "')";

                    MyCommand = new OdbcCommand(updateSQL, MyConnection);
                    MyCommand.ExecuteNonQuery();
                    MyCommand = new OdbcCommand(updateSQL2, MyConnection);
                    MyCommand.ExecuteNonQuery();
                }

                else
                {
                    MessageBox.Show("Item has already been checked in");
                }

                //Close all resources and disconnects from the database
                MyDataReader.Close();
                MyConnection.Close();
                return 1;
            }

            catch (Exception)
            {
                return -1;
            }
        }

        public List<ListViewItem> memberHistory(String memId)
        {
            try
            {
                //Opens the connection to the database
                MyConnection.Open();

                // Declares and build the first half of the SQL query. The "WHERE" part of the query is worked on in the for loop.
                List<ListViewItem> returnList = new List<ListViewItem>();
                string sqlStatement = "SELECT F.Style, F.Category, F.Color, F.Manufacturer, R.checkoutdate, R.checkoutempid, R.checkindate, R.checkinempid, R.rentalnumber FROM FURNITURE_ITEM AS F, RENTAL AS R WHERE R.memberid = " + memId + " AND F.itemNumber = R.itemnumber ORDER BY F.isAvailable, R.checkoutdate";

                //Declares and initializes new OdbcCommand
                MyCommand = new OdbcCommand(sqlStatement, MyConnection);

                //Initializes a data reader
                MyDataReader = MyCommand.ExecuteReader();

                //Runs through the returned data and builds a string that holds the search results.
                while (MyDataReader.Read())
                {
                    //Populates each listViewItem with the information about a furniture item.
                    ListViewItem historyList = new ListViewItem(MyDataReader.GetString(0));
                    historyList.SubItems.Add(MyDataReader.GetString(1));
                    historyList.SubItems.Add(MyDataReader.GetString(2));
                    historyList.SubItems.Add(MyDataReader.GetString(3));
                    historyList.SubItems.Add(MyDataReader.GetString(4));
                    historyList.SubItems.Add(MyDataReader.GetString(5));
                    historyList.SubItems.Add(MyDataReader.GetString(6));
                    historyList.SubItems.Add(MyDataReader.GetString(7));
                    historyList.SubItems.Add(MyDataReader.GetString(8));
                    returnList.Add(historyList);
                }

                //Close all resources and disconnects from the database
                MyDataReader.Close();
                MyConnection.Close();

                return returnList;
            }
            // Catches any errors that may have occured
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
            }
        }

        public String sysAdminExecuteSql(String sqlStatement)
        {
            try
            {
                // Opens connection to Database
                MyConnection.Open();

                // Builds SQL query
                OdbcCommand MyCommand = new OdbcCommand(sqlStatement, MyConnection);

                // Declares and initialized data reader.
                OdbcDataReader MyDataReader;
                MyDataReader = MyCommand.ExecuteReader();

                StringBuilder resultFromQuery = new StringBuilder("");
                
                // Loops through the values returned by the database and builds the result
                while (MyDataReader.Read())
                {
                    int numOfColumns = MyDataReader.FieldCount;
                    for (int i = 0; i < numOfColumns; i++)
                    {
                        resultFromQuery.Append(MyDataReader.GetString(i));
                        resultFromQuery.Append(" ");
                        
                    }

                    resultFromQuery.Append(" \r\n");
                    
                }

                //Close all resources and disconnect
                MyDataReader.Close();
                MyConnection.Close();

                return resultFromQuery.ToString();
            }
            catch (Exception e)
            {
                return e.Message;
            }



        }
    
    #endregion
    
    }

}
